<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<jsp:include page="common/auth.jsp" />
<jsp:include page="common/header.jsp" />
<body>
  <jsp:include page="common/topmenu-owner.jsp" />

<%@ page language="java" import="java.sql.*" %>
<%@ page language="java" import="java.util.ArrayList" %>
<%@ page language="java" import="db.Config" %>
<%@ page language="java" import="db.Filter" %>
<%@ page language="java" import="db.Row" %>
<%@ page language="java" import="db.Product" %>
<%-- -------- Open Connection Code -------- --%>

<%
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;

  String source = request.getParameter("source");
  if (source == null)
    source = "xql";

	try {
    // Load JDBC Driver class file
    Class.forName(Config.jdbcDriver);

    // Open a connection to the database using DriverManager
    //conn = DriverManager.getConnection(Config.connectionURL, Config.username, Config.password);
    conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/" + source, Config.username, Config.password);


		ArrayList<String> prodIdList = new ArrayList<String>();

		ArrayList<String> idList = new ArrayList<String>();
    ArrayList<String> nameList = new ArrayList<String>();
    pstmt = conn.prepareStatement("SELECT id, name FROM category");
    rs = pstmt.executeQuery();
    while ( rs.next() ) {
      idList.add(rs.getString("id"));
      nameList.add(rs.getString("name"));
    }

    String action = request.getParameter("action");

    String rowName = "customer";
    String age = "all";
    String state = "all";
    String category = "all";
    String quarter = "all";

    Filter custFilter = new Filter();
    Filter custStateFilter = new Filter();
    Filter prodFilter = new Filter();
    Filter quarterFilter = new Filter();
    
    int pageNum = 0;
    int lastPage = -1;

    if (request.getParameter("pageNum") != null)
      pageNum = Integer.parseInt(request.getParameter("pageNum"));

    if (action != null && action.equals("query")) {
      rowName = request.getParameter("rowname");
      age = request.getParameter("age");
      state = request.getParameter("state");
      category = request.getParameter("category");
      quarter = request.getParameter("quarter");
      
      if (age != null && !age.equals("all")) {
        String[] range = age.split("~");
        custFilter.add("age >= " + range[0] + " AND " + "age <= " + range[1]);
        custStateFilter.add("age >= " + range[0] + " AND " + "age <= " + range[1]);
      }

      if (state!= null && !state.equals("all")) {
        custFilter.add("state = '" + state + "'");
        custStateFilter.add("user.state = '" + state + "'");
      }

      if (quarter != null && !quarter.equals("all")) {
        custFilter.add("quarter = '" + quarter + "'");
        prodFilter.add("quarter = '" + quarter + "'");
        quarterFilter.add("quarter = '" + quarter + "'");
      }

      if (category != null && !category.equals("all")) {
        prodFilter.add("category = " + category);
      }
    }

    String prodQuery = quarterFilter.toSQL().equals("")?
                       "SELECT product_id, name, SUM(amount) AS amount " + 
                       "FROM product_agg " +
                       prodFilter.toSQL() + " " +
                       "GROUP BY product_id " +
                       "ORDER BY SUM(amount) DESC LIMIT " + Config.colLimit
                       :
                       "SELECT product_id, name, SUM(amount) AS amount, quarter " + 
                       "FROM product_agg " +
                       prodFilter.toSQL() + " " +
                       "GROUP BY product_id " +
                       "ORDER BY SUM(amount) DESC LIMIT " + Config.colLimit;


    String custQuery = "SELECT user_id, state, name, SUM(cost) AS cost " + 
                       "FROM customer_agg " + 
                       custFilter.toSQL() + " " +
                       "GROUP BY user_id " +
                       "ORDER BY SUM(cost) DESC LIMIT " + (pageNum * Config.rowLimit) +", " + Config.rowLimit;


    String stateQuery = "SELECT state, SUM(cost) AS cost " + 
                       "FROM customer_agg " + 
                       custFilter.toSQL() + " " +
                       "GROUP BY state " +
                       "ORDER BY state LIMIT " + (pageNum * Config.rowLimit) +", " + Config.rowLimit;

    custStateFilter.add("role = 'user'");
    custStateFilter.add("user.state = sq.state");
    String custStateQuery = "SELECT id user_id, user.state state, cost " + 
                            "FROM user, (" + stateQuery + ") sq " +  
                            custStateFilter.toSQL() + " " + 
                            "GROUP BY user_id";

    //System.out.println(prodQuery);
    //System.out.println(custQuery);
    //System.out.println(stateQuery);
    //System.out.println(custStateQuery);
%>


  <div class="container-fluid">
  	<div class="row-fluid">
  	<form action="analytics.jsp" method="post">
      <input type="hidden" name="action" value="query" />
  		<div class="span1 offset1">
  			<div class="control-group">
	         	<label class="control-label">Row Name</label>
	         	<div class="controls">
		          	<select name="rowname" class="input-medium">
                  <option value="customer" <%= (rowName.equals("user"))? "selected" : "" %>>customer</option>
		          		<option value="state" <%= (rowName.equals("state"))? "selected" : "" %>>state</option>                           
		          	</select>  
		        </div>
	  		</div>
	  	</div>
      
    <div class="span1 offset1">
	    <div class="control-group">
	      <label class="control-label">Age</label>
	      <div class="controls">
	        <select name="age" class="input-small">
	        	<option value="all">All</option>
	        <%
	        	for (int i = 0; i < 10; ++i){
	        %>
            <option value="<%= i*10%>~<%=i*10+9%>" <%= (age.equals(i*10 + "~" + (i*10 + 9)))? "selected" : "" %>><%= i*10%>~<%=i*10+9%></option>       
          <%
	        	}
	        %>              
	        </select>  
	      </div>
	    </div>
		</div>

		<div class="span1">
      <div class="control-group">
       <label class="control-label">State</label>
       <div class="controls">
         <select name="state" class="input-small">
           <option value="all">All</option>
         <%
           int size = Config.states.length;
           for(int i = 0; i < size; ++i) {
         %>
         <option value="<%= Config.states[i]%>" <%= (state.equals(Config.states[i]))? "selected" : "" %>><%= Config.states[i]%></option>
         <%
           }
         %>
         </select>  
       </div>
     </div>
		</div>
      
		<div class="span1">
	    <div class="control-group">
        <label class="control-label">Category</label>
        <div class="controls">
          <select name="category" class="input-small">
            <option value="all">All</option>
          <%
            size = idList.size();
            for (int i = 0; i < size; ++i) {
          %>
            <option value="<%= idList.get(i)%>" <%= (category.equals(idList.get(i)))? "selected" : ""%>><%= nameList.get(i)%></option>                        
          <%
            }
          %>
          </select>  
        </div>
      </div>
    </div>

		<div class="span1">
	      <div class="control-group">
	        <label class="control-label">Quarter</label>
	        <div class="controls">
	          <select name="quarter" class="input-small">
	            <option value="all">All</option>        
	          	<option value="fall" <%= (quarter.equals("fall"))? "selected" : "" %>>Fall 2012</option>
	          	<option value="winter" <%= (quarter.equals("winter"))? "selected" : "" %>>Winter 2012</option>
	          	<option value="spring" <%= (quarter.equals("spring"))? "selected" : "" %>>Spring 2013</option>
	          	<option value="summer" <%= (quarter.equals("summer"))? "selected" : "" %>>Summer 2013</option>                                   
	          </select>  
	        </div>
	      </div>
	     </div>

		<div class="span1">
	      <div class="control-group">
	        <label class="control-label">Source</label>
	        <div class="controls">
	          <select name="source" class="input-small">
	          	<option value="xql" <%= (source.equals("xql"))? "selected" : "" %>>Demo</option>
	          	<option value="performance" <%= (source.equals("performance"))? "selected" : "" %>>Performance</option>
	          </select>  
	        </div>
	      </div>
	     </div>
	     
     <div class="span2 offset1">
	      <div class="control-group">
	        <label class="control-label"></label>
	        <div class="controls">
	        	<button class="btn btn-primary" type="submit">Run Query</button>
	        </div>
	      </div>
	     </div>

	      	</div>
	     </form>
	    </div>
	    
	    <div class="span11 offset1">
        <table class="table table-hover">
          <%
            if (rowName.equals("state")) {
              if (custFilter.toSQL().equals("")) {
              pstmt = conn.prepareStatement("SELECT sp.state row_name, sp.cost row_cost, sp.product_name product_name, sp.amount product_amount, spa.total AS amount, spa.count AS count " +
                                            "FROM state_product_agg spa " +
                                              "RIGHT OUTER JOIN " +
                                              "(SELECT state, cost, product_id, p.name product_name, amount " + 
                                              "FROM (" + stateQuery + ") s, (" + prodQuery + ") p) sp " +
                                            "ON spa.state = sp.state AND spa.product = sp.product_id " +
                                            "GROUP BY sp.state, sp.product_id " +
                                            "ORDER BY sp.state, sp.amount DESC");
              } else {
              pstmt = conn.prepareStatement(quarterFilter.toSQL().equals("")?
                "SELECT cp.state row_name, cp.cost row_cost, cp.product_name product_name, cp.amount product_amount, SUM(o.total) AS amount, COUNT(o.total) AS count " +
                "FROM `order` o " +
                "RIGHT OUTER JOIN " +
                  "(SELECT user_id, state, cost, product_id, p.name product_name, amount " + 
                  "FROM (" + custStateQuery + ") c, (" + prodQuery + ") p) cp " +
                "ON o.user = cp.user_id AND o.product = cp.product_id " +
                "GROUP BY cp.state, cp.product_id " +
                "ORDER BY cp.state, cp.amount DESC"
                :
                "SELECT cp.state row_name, cp.cost row_cost, cp.product_name product_name, cp.amount product_amount, SUM(o.total) AS amount, COUNT(o.total) AS count " +
                "FROM `order` o " +
                "RIGHT OUTER JOIN " +
                  "(SELECT user_id, state, cost, product_id, p.name product_name, amount, quarter " + 
                  "FROM (" + custStateQuery + ") c, (" + prodQuery + ") p) cp " +
                "ON o.user = cp.user_id AND o.product = cp.product_id AND o.quarter = cp.quarter " +
                "GROUP BY cp.state, cp.product_id " +
                "ORDER BY cp.state, cp.amount DESC");
              }
            }
            else {
              pstmt = conn.prepareStatement(quarterFilter.toSQL().equals("")?
                                            "SELECT cp.user_name row_name, cp.cost row_cost, cp.product_name product_name, cp.amount product_amount, SUM(o.total) AS amount, COUNT(o.total) AS count " +
                                            "FROM `order` o " +
                                              "RIGHT OUTER JOIN " +
                                              "(SELECT user_id, c.name user_name, cost, product_id, p.name product_name, amount " + 
                                              "FROM (" + custQuery + ") c, (" + prodQuery + ") p) cp " +
                                            "ON o.user = cp.user_id AND o.product = cp.product_id " +
                                            "GROUP BY cp.user_id, cp.product_id " +
                                            "ORDER BY cp.cost DESC, cp.amount DESC"
                                            :
                                            "SELECT cp.user_name row_name, cp.cost row_cost, cp.product_name product_name, cp.amount product_amount, SUM(o.total) AS amount, COUNT(o.total) AS count " +
                                            "FROM `order` o " +
                                              "RIGHT OUTER JOIN " +
                                              "(SELECT user_id, c.name user_name, cost, product_id, p.name product_name, amount, quarter " + 
                                              "FROM (" + custQuery + ") c, (" + prodQuery + ") p) cp " +
                                            "ON o.user = cp.user_id AND o.product = cp.product_id AND o.quarter = cp.quarter " +
                                            "GROUP BY cp.user_id, cp.product_id " +
                                            "ORDER BY cp.cost DESC, cp.amount DESC"
                                            );
            }

            rs = pstmt.executeQuery();

            ArrayList<Row> rows = new ArrayList<Row> ();
            Product[] products = new Product[Config.colLimit];
            int numOfProduct = 0;
            int i = 0;
            if (rs.next()) {
              Row r = new Row(rs.getString("row_name"), rs.getFloat("row_cost"));
              while (!rs.isAfterLast() && rs.getString("row_name").equals(r.name)) {
              	products[i] = new Product(rs.getString("product_name"), rs.getInt("product_amount"));
                r.amount[i] = rs.getFloat("amount");
                r.count[i] = rs.getInt("count");
                i++;
                rs.next();
              }
              rows.add(r);

              numOfProduct = i;
              
              while (!rs.isAfterLast()) {
                r = new Row(rs.getString("row_name"), rs.getFloat("row_cost"));
                for (i = 0; i < numOfProduct; i++) {
                  r.amount[i] = rs.getFloat("amount");
                  r.count[i] = rs.getInt("count");
                  rs.next();
                }
                rows.add(r);
              }
            }
          %>

          <tr>
            <th></th>
            <%
              for (Product p : products) {
                if (p != null) {
            %>
            <th><%= p.name %> ( <%= p.amount %> )</th>
            <%
                } else {
            %>
            <th></th>
            <%
                }
              }
            %>	  
          </tr>

          <%
            for (Row row : rows) {
          %>
          <tr>
            <th><%= row.name %> ( $<%= String.format("%.2f", row.cost) %> )</th>
            <%
              for (int j = 0; j < numOfProduct; j++) {
            %>
              <td>$<%= String.format("%.2f", row.amount[j]) %> in <%= row.count[j] %></td>
            <%
              }
            %>
          </tr>
          <%
            }
            
            // hit last page
            if (rows.size() < Config.rowLimit)
              lastPage = pageNum;
          %>
        </table>

        <!-- Prev/next pagination -->
        <ul class="pager">
        <%
          if (pageNum <= 0) {
        %>
          <li class="previous disabled">
            <a href="#">&larr; Prev</a>
          </li>
        <%
          }
          else {
        %>
          <li class="previous">
          <a href="analytics.jsp?pageNum=<%= pageNum - 1 %>&action=query&rowname=<%= rowName %>&age=<%= age %>&state=<%= state %>&category=<%= category %>&quarter=<%= quarter %>&source=<%= source %>">&larr; Prev</a>
          </li>
        <%
          }
          if (lastPage != -1 && pageNum >= lastPage) {
        %>
          <li class="next disabled">
            <a href="#">Next &rarr;</a>
          </li>
        <%
          }
          else {
        %>
          <li class="next">
          <a href="analytics.jsp?pageNum=<%= pageNum + 1 %>&action=query&rowname=<%= rowName %>&age=<%= age %>&state=<%= state %>&category=<%= category %>&quarter=<%= quarter %>&source=<%= source %>">Next &rarr;</a>
          </li>
        <%
          }
        %>
        </ul>
      </div>
	  </div>

<%-- -------- Close Connection Code -------- --%>
<%
  // Close the ResultSet
  rs.close();

  // Close the Statement
  pstmt.close();

  // Close the Connection
  conn.close();
} catch (SQLException e) {
  
  // Wrap the SQL exception in a runtime exception to propagate
  // it upwards
  String errorMsg = e.getMessage();
  //TODO: Parse error message to display user-friendly messages.
%>
  <!-- Display error message -->
  <div class="container">
    <div class="alert alert-error">
      <button type="button" class="close" onclick="window.location.href='categories.jsp'">&times;</button>
      <strong>Error!</strong> <%= errorMsg %>
    </div>
  </div>
<%
}
finally {
  // Release resources in a finally block in reverse-order of
  // their creation

  if (rs != null) {
    try {
      rs.close();
    } catch (SQLException e) { } // Ignore
    rs = null;
  }
  if (pstmt != null) {
    try {
      pstmt.close();
    } catch (SQLException e) { } // Ignore
    pstmt = null;
  }
  if (conn != null) {
    try {
      conn.close();
    } catch (SQLException e) { } // Ignore
    conn = null;
    }
  }
%>
  <script src="js/jquery-1.9.1.js"></script>
  <script src="js/bootstrap.min.js"></script>
  <script>
    $(document).ready(function() {
      $('#nav-analytics').addClass('active');
    });
  </script>
</body>
</html>
